const express = require("express");
const app = express();
// var url = require("url");
var bodyParser = require("body-parser"); //格式化请求消息的中间件
// const db = require("./db/db.js");
const mysql = require('mysql')
const config = require('./db/mysql');

const connection = mysql.createConnection(config)

// app.use(bodyParser.urlencoded({ extended: false }));
app.use(express.json());
// // //设置允许跨域的域名，*代表允许任意域名跨域
app.all("*", function (req, res, next) {
  res.header("Access-Control-Allow-Origin", "*"); //允许的header类型
  res.header("Access-Control-Allow-Headers", "Content-type"); //跨域允许的请求方式
  res.header(
    "Access-Control-Allow-Methods",
    "PUT,POST,GET,DELETE,OPTIONS,PATCH"
  ); //可选，用来指定本次预检请求的有效期，单位为秒。在此期间，不用发出另一条预检请求。
  res.header("Access-Control-Max-Age", 1728000); //预请求缓存20天
  next();
});

// 号箱列表
app.get('/list/boxs', (req, res, next) => {
  const { pageSize = 10, currentPage = 1, box_number = null } = req.query
  const offset = (currentPage - 1) * pageSize;
  connection.query(`SELECT COUNT(*) AS count FROM boxs`,(error, results) => {
    if( error ) return error
    const total = results[0].count;
    connection.query(`select * from boxs LIMIT ${ box_number ? `where box_number = ${ box_number }` : '' } ${pageSize} OFFSET ${offset}`, (err, result) => {
      if (err) return  console.log('err', err);
      res.send({ msg:"获取成功", code:1, data:{ list:result, total } })
    })
  })
})

// 添加号箱，并绑定型号
app.post('/addBox',(req,res,next) => {
  console.log('进入请求了',req.body);
  const { box_number, model } = req.body
  const addSQl = `INSERT INTO boxs (box_number, model) VALUES (?, ?);`
  connection.query(addSQl, [ box_number,model ], (err, result) => {
    if (err) {
      if( err.code === 'ER_DUP_ENTRY' ) {
        let msg = err.sqlMessage.indexOf('box_number') != -1 ? '重复的箱子号' : '重复的型号'
        return res.send({ msg, code:0, data:null })
      }
      return console.log('添加号箱', err);
    }
    res.send({ msg:'添加箱子号成功', code:1, data:null})
  })
})

// 编辑箱子号以及型号
app.post('/updateBoxs',(req,res,next) => {
  console.log('进入请求了',req.body);
  const { box_number,model,id } = req.body
  const updateSql = "update boxs set box_number=?,model=? where id = ?"
  connection.query('SELECT * FROM products WHERE box_id = ?;',[id], (err1, result1) => {
    if (err1)  return console.log('编辑箱号前的查询', err1);
    if ( result1.length ) return res.send({ msg:'该箱号下已有绑定的产品，请先清空所绑定的产品再进行操作', code:0, data:null})
    connection.query(updateSql, [box_number,model,id] , (err, result) => {
      if (err) {
        if( err.code === 'ER_DUP_ENTRY' ) {
          let msg = err.sqlMessage.indexOf('box_number') != -1 ? '重复的箱子号' : '重复的型号'
          return res.send({ msg, code:0, data:null })
        }
        return console.log('编辑号箱', err);
      }
      res.send({ msg:'编辑成功', code:1, data:null})
    })
  })
})

// 删除号箱
app.get('/delBoxs',(req,res,next) => {
  console.log('进入请求了',req.query);
  connection.query('SELECT * FROM products WHERE box_id = ?;',[req.query.id], (err1, result1) => {
    if (err1)  return console.log('删除箱号前的查询', err1);
    if ( result1.length ) return res.send({ msg:'该箱号下已有绑定的产品，请先清空所绑定的产品再进行操作', code:0, data:null})
    connection.query("delete from boxs where id = ?", [req.query.id] , (err, result) => {
      if (err)  return console.log('err', err);
      // 将 MySQL 查询结果作为路由返回值
      res.send({ msg:`删除箱子号成功,已成功删除${result.affectedRows}条`, code:1, data:null})
    })
  })
})

app.get('/list/products', (req, res, next) => {
  const { pageSize = 10, currentPage = 1, box_number = null, model = null, color = null, num = null, sortStr = 'box_number', sortType = 'ASC' } = req.query
  const offset = (currentPage - 1) * pageSize;
  let sql = `SELECT COUNT(*) AS count FROM products`;
  let whereClause = '';
  let params = [];
  if (box_number || model || color) {
    whereClause = ' WHERE ';
    if (box_number) {
      whereClause += 'box_number = ?';
      params.push(box_number);
    }
    if (model) {
      if (params.length > 0) {
        whereClause += ' AND ';
      }
      whereClause += 'model = ?';
      params.push(model);
    }
    if (color) {
      if (params.length > 0) {
        whereClause += ' AND ';
      }
      whereClause += 'color LIKE ?';
      params.push(`%${color}%`);
    }
  }
  sql += whereClause;
  connection.query(sql, params, function (error, countResults, fields) {
    if (error) throw error;
    const count = countResults[0].count;
    sql = `SELECT * FROM products`;
    sql += whereClause;
    sql += ` ORDER BY ${sortStr} ${sortType} LIMIT ? OFFSET ?`;
    params.push(Number(pageSize));
    params.push(offset);
    connection.query(sql, params, (err, result) => {
      if (err) {
        console.log('err', err);
      } else {
        // 将 MySQL 查询结果作为路由返回值
        console.log('res', result);
        res.send({
          msg:'获取成功',
          code:1,
          data:{
            list:result,
            total:count
          }
        })
      }
    })
  })
})



app.post('/addProducts',(req,res,next) => {
  console.log('进入请求了',req.body);
  const { box_number,model,color,num,box_id } = req.body
  const addSQl = `INSERT INTO products (box_number,model,color,num,box_id) values(?,?,?,?,?);`
  const queryColor = `select * from products where box_number = ? and color = ?`
  connection.query(queryColor, [ box_number, color.trim()], (err1, result1) => {
    if (err1) return console.log(err1);
    if ( result1.length ) return res.send({ msg:'该号箱下已有相同的颜色，请检查', code:0, data:null})
     connection.query(addSQl, [box_number,model,color,num,box_id] , (err, result) => {
      if (err) return console.log(err);
      res.send({ msg:'添加产品成功', code:1, data:null})
    })
  })
})


app.get('/delProducts',(req,res,next) => {
  console.log('进入请求了',req.query);
  connection.query("delete from products where id in ?", [[req.query.ids]] , (err, result) => {
    if (err) return console.log('删除产品', err);
    res.send({ msg:`删除产品成功,已成功删除${result.affectedRows}条`, code:1, data:null})
  })
})

app.post('/updateProducts',(req,res,next) => {
  const { box_number,model,color,num,id } = req.body
  const updateSql = "update products set box_number=?,model=?,color=?,num=? where id = ?"
  const queryColor = `select * from products where box_number = ? and color = ? and id != ?`
  connection.query(queryColor, [ box_number, color.trim(), id], (err1, result1) => {
    if (err1) return console.log(err1);
    console.log('result1', result1);
    if ( result1.length ) return res.send({ msg:'该号箱下已有相同的颜色，请检查', code:0, data:null})
    connection.query(updateSql, [box_number,model,color,num,id] , (err, result) => {
      if (err) return console.log('编辑产品',err);
      res.send({ msg:`编辑成功`, code:1, data:null})
    })
  })
})

app.listen(3000, () => console.log("Example app listening on port 3000!"));

